Configuring: Transpose

When configuring a pivot table, the most important step is to decide what information you would like to know from the dataset. Once you have formulated the one question that needs answering, configuring the pivot operation is easy. For example, your question could be: "During which shift on which day of the week do I get the greatest production?"

Thus configure the operation as follows:

group = shift
pivot field = day
class = Monday for the first class, Tuesday for the next class, Wednesday for the next and so on.
aggregation = sum

If selected and configured, the aggregations can also be presented on groups within the fields, instead of the field as a whole. The newly created fields in the pivot dataset can then be prioritized by using the sorting option.

Worked example: Transpose

Steps to configure

Group fields: Optional: Aggregations can also be presented on groups within the fields, instead of the field as a whole. Select the field to be used for grouping if required.
Select pivot field: Select the field that you want to use as the pivot field.
Aggregate fields: Specify the class and field on which the aggregation will be performed. Provide a descriptive name for the new fields that will be formed in the resulting pivot dataset.
Order fields: Optional: The newly created fields in the pivot dataset can be displayed in a prioritized manner by specifying the sorting order of the fields.
Execute: A new pivot dataset is created, presenting aggregations of the selected fields.

Step 1: Group fields

Aggregations can also be presented on groups within the fields, instead of the field as a whole. Select the field to be used for grouping if required.

This is an optional step.
You can only group by the fields in the original dataset.
The case sensitivity of this field is determined by your installation configuration of SQL Server. Default installation of SQL Server is case insensitive.
In our example, group = shift. We want the results to be presented per shift - a subgroup of the selected pivot field Day.
Default: All available fields are listed in the available column.

Step 2: Select pivot field

Select your pivot field.

The pivot field is the field containing variables that will "control" and define the structure of the data summary.
In our example, the pivot field is field: Day, as we will then determine the aggregations within the control structure of each day, aggregations will also be presented for each day.
Default: The fields are placed in the available column.
Minimum configuration: Only one field must be selected.

Step 3: Aggregate fields

Specify the class and field on which the selected aggregation will be performed. Provide a descriptive name for the new fields that will be formed in the resulting pivot dataset.

Class:

A variable within the pivot field defining which data in corresponding field rows must be used for the aggregation.
In our example, each day is a class as aggregation on production must be calculated per day.
More than one class can be created.
The drop down menu will list all possible classes within the pivot field.

Field:

The data values on which the aggregate will be performed.
In our example, field = production as we wish to receive a summary of the production values.
The drop down menu will list all possible fields for aggregation.

Aggregation:

The list of possible aggregates is given.
Refer to Grouping and Aggregation for details on each specific function.
In our example, aggregation = sum as we want to know the sum of production for each day.

Name:

The name of the new field that will be created within the pivot dataset, containing the aggregate information.
A name is automatically generated, comprising: [Aggregation]_[Class]_[Field].
This name can be edited but has to be a valid name.
In our example, name = Sum_Monday_Production. We are looking for the sum of all production per day, which is a class of the pivot field.

[Add]:

This will add the specified aggregation function per class and field to the list displayed in the window.
Any number of class, field, and aggregation combinations can be defined.

Default:
- The name will be auto-generated based on the aggregate, class and field selected.
Minimum configuration: At least one aggregate has to be added.

Step 4: Order fields

The newly created fields in the pivot dataset can be displayed in a prioritized manner by specifying the sorting order of the fields.

This is an optional step.
You can only order the newly created fields that will be displayed in the pivot dataset, or by the grouping fields.
In our example, the order selected is [shift], then Sum_Monday_Production, Sum_Tues_Prod etc, corresponding to the days of the week.
Default: All the fields are placed in the available column.

Step 5: Execute

A new pivot dataset is created, presenting aggregations summarizing the data within selected fields, defined by classes within the pivot field.